﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;
using Models;
using Server.Common;

namespace Server.DAL
{
    public static class CodeSrv
    {
        #region 转换

        private static CodeModel DrToCode(DataRow dr)
        {
            if (null == dr)
                return null;
            CodeModel cm = new CodeModel
            {
                CodeFull = Comm.Cdr<string>(dr, "code_full"),
                CodeMain = Comm.Cdr<string>(dr, "code_main"),
                CodeNum = Comm.Cdr<int>(dr, "code_num"),
                CodePro = Comm.Cdr<string>(dr, "code_pro"),
                CodeSuffix = Comm.Cdr<string>(dr, "code_suffix"),
                CodeYear = Comm.Cdr<string>(dr, "code_year"),
                GenDate = Comm.Cdr<string>(dr, "gen_date"),
                Id = Comm.Cdr<int>(dr, "id"),
                OperatorId = Comm.Cdr<int>(dr, "operator_id"),
                ProId = Comm.Cdr<int>(dr, "pro_id"),
                Status = Comm.Cdr<byte>(dr, "status")
            };
            return cm;
        }

        private static CodeStatusModel DrToStatus(DataRow dr)
        {
            if (null == dr)
                return null;
            CodeStatusModel cs = new CodeStatusModel
            {
                ChargeAmount = Comm.Cdr<decimal>(dr, "charge_amount"),
                ChargeDate = Comm.Cdr<string>(dr, "charge_date"),
                ChargeFlag = Comm.Cdr<byte>(dr, "charge_flag"),
                ChargeMaker = Comm.Cdr<int>(dr, "charge_maker"),
                ChargerId = Comm.Cdr<int>(dr, "charge_custom"),
                CodeBaseInfo = null,
                CodeId = Comm.Cdr<int>(dr, "code_id"),
                SaleCustId = Comm.Cdr<Int32>(dr, "sale_custom"),
                SaleDate = Comm.Cdr<string>(dr, "sale_date"),
                SaleFlag = Comm.Cdr<byte>(dr, "sale_flag"),
                SaleMaker = Comm.Cdr<int>(dr, "sale_maker")
            };
            cs.CodeBaseInfo = DrToCode(dr);
            return cs;
        }

        #endregion 转换

        #region 增加一个码

        //单个增加
        public static void AddCode(CodeModel cm)
        {
            string sqlstr = $"insert into t_code(pro_id,code_full,code_pro,code_year,code_num,code_main,code_suffix,operator_id) values({cm.ProId},'{cm.CodeFull}','{cm.CodePro}','{cm.CodeYear}',{cm.CodeNum},'{cm.CodeMain}','{cm.CodeSuffix}',{cm.OperatorId});insert into t_code_status(code_id) select id from t_code where id not in(select code_id from t_code_status);";
            Comm.DB.Exec(sqlstr);
        }

        //批量增加
        public static void AddCodeBatch(List<CodeModel> codeList)
        {
            string sqlstr = "insert into t_code(pro_id,code_full,code_pro,code_year,code_num,code_main,code_suffix,operator_id) values(@pro_id,@code_full,@code_pro,@code_year,@code_num,@code_main,@code_suffix,@operator_id)";
            SQLiteParameter[][] op = new SQLiteParameter[codeList.Count][];
            for (int i = 0; i < codeList.Count; i++)
            {
                var c = codeList[i];
                op[i] = new SQLiteParameter[]
                {
                    new SQLiteParameter {DbType=DbType.Int32,ParameterName="@pro_id",Value=c.ProId},
                    new SQLiteParameter {DbType=DbType.String,ParameterName="@code_full",Value=c.CodeFull},
                    new SQLiteParameter {DbType=DbType.String,ParameterName="@code_pro",Value=c.CodePro},
                    new SQLiteParameter {DbType=DbType.String,ParameterName="@code_year",Value=c.CodeYear},
                    new SQLiteParameter {DbType=DbType.Int32,ParameterName="@code_num",Value=c.CodeNum},
                    new SQLiteParameter {DbType=DbType.String,ParameterName="@code_main",Value=c.CodeMain},
                    new SQLiteParameter {DbType=DbType.String,ParameterName="@code_suffix",Value=c.CodeSuffix},
                    new SQLiteParameter {DbType=DbType.Int32,ParameterName="@operator_id",Value=c.OperatorId}
                };
            }
            Comm.DB.ExecTrans(sqlstr, op);
            sqlstr = "insert into t_code_status(code_id) select id from t_code where id not in(select code_id from t_code_status);";
            Comm.DB.Exec(sqlstr);
        }

        #endregion 增加一个码

        #region 查询单个码信息

        //获取完整码信息
        public static CodeStatusModel GetCodeStatusInfo(int codeId)
        {
            string sqlstr = "select t.*,t1.* from t_code t left join t_code_status t1 on t.id=t1.code_id where t.id=" + codeId;
            var dt = Comm.DB.Query(sqlstr);
            if (null == dt)
                throw new Exception("未知错误");
            if (dt.Rows.Count < 1)
                throw new Exception("查询编码信息失败！");
            return DrToStatus(dt.Rows[0]);
        }

        public static CodeStatusModel GetCodeStatusInfo(string codeFull)
        {
            string sqlstr = $"select t.*,t1.* from t_code t left join t_code_status t1 on t.id=t1.code_id where t.code_full='{codeFull}'";
            var dt = Comm.DB.Query(sqlstr);
            if (null == dt)
                throw new Exception("未知错误");
            if (dt.Rows.Count < 1)
                throw new Exception("查询编码信息失败！");
            return DrToStatus(dt.Rows[0]);
        }

        //获取码信息
        public static CodeModel GetCodeInfo(int codeId)
        {
            string sqlstr = "select * from t_code where id=" + codeId;
            var dt = Comm.DB.Query(sqlstr);
            if (null == dt)
                throw new Exception("未知错误");
            if (dt.Rows.Count < 1)
                return null;
            return DrToCode(dt.Rows[0]);
        }

        public static CodeModel GetCodeInfo(string codeFull)
        {
            string sqlstr = $"select * from t_code where code_full='{codeFull}'";
            var dt = Comm.DB.Query(sqlstr);
            if (null == dt)
                throw new Exception("未知错误");
            if (dt.Rows.Count < 1)
                return null;
            return (DrToCode(dt.Rows[0]));
        }

        #endregion 查询单个码信息

        #region 读取码列表

        public static List<CodeModel> GetCodeList(int proId)
        {
            string sqlstr = "select * from t_code where pro_id=" + proId;
            var dt = Comm.DB.Query(sqlstr);
            if (null == dt)
                throw new Exception("未知错误");
            if (dt.Rows.Count < 1)
                return null;
            List<CodeModel> l = new List<CodeModel>(dt.Rows.Count);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                l.Add(DrToCode(dr));
            }
            return l;
        }

        public static List<CodeModel> GetCodeList(string proCode)
        {
            string sqlstr = $"select * from t_code where code_pro='{proCode}'";
            var dt = Comm.DB.Query(sqlstr);
            if (null == dt)
                throw new Exception("未知错误");
            if (dt.Rows.Count < 1)
                return null;
            List<CodeModel> l = new List<CodeModel>(dt.Rows.Count);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                l.Add(DrToCode(dr));
            }
            return l;
        }

        public static List<CodeModel> GetCodeList(string year, string suffix)
        {
            string sqlstr = $"select * from t_code where code_year='{year}' and status=0";
            if (!string.IsNullOrEmpty(suffix))
                sqlstr += $" and code_suffix='{suffix}'";
            else
                sqlstr += $" and code_suffix is NULL";
            var dt = Comm.DB.Query(sqlstr);
            if (null == dt)
                throw new Exception("未知错误");
            if (dt.Rows.Count < 1)
                return null;
            List<CodeModel> l = new List<CodeModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                l.Add(DrToCode(dr));
            }
            return l;
        }

        #endregion 读取码列表

        #region 读码状态列表

        public static List<CodeStatusModel> GetStatusList(string sDate, string eDate, int proId = 0, byte status = 255)
        {
            sDate = Convert.ToDateTime(sDate).ToString("yyyy-MM-dd 00:00:00");
            eDate = Convert.ToDateTime(eDate).ToString("yyyy-MM-dd 23:59:59");
            string sqlstr = $"select t.*,t1.* from t_code t left join t_code_status t1 on t.id = t1.code_id where t.gen_date>='{sDate}' and t.gen_date<='{eDate}'";
            if (status != 255)
                sqlstr += $" and t.status={status}";
            if (proId > 0)
                sqlstr += $" and t.pro_id={proId}";
            var dt = Comm.DB.Query(sqlstr);
            if (null == dt)
                throw new Exception("未知错误");
            if (dt.Rows.Count < 1)
                return null;
            List<CodeStatusModel> l = new List<CodeStatusModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                l.Add(DrToStatus(dr));
            }
            return l;
        }

        #endregion 读码状态列表

        #region 空码删除

        public static void CancelCode(int codeId)
        {
            string sqlstr = $"delete from t_code where id={codeId};delete from t_code_status where code_id={codeId};";
            Comm.DB.Exec(sqlstr);
        }

        #endregion 空码删除
    }
}